package com.anolesoft.yr.mbus.pre.sql;

public class PrePromoteSQL {

    //新增时查询推广记录编号是否已经存在
    public static final String QUERY_PRE_PROMOTE_BY_CODE = "select count(*) as count from pre_promote a where a.del_flag=0 and a.promote_code=#{promoteCode}";

    //新增推广记录
    public static final String SAVE_PRE_PROMOTE = "insert into pre_promote(objuid,fac_code,promote_code,info_source,source_time,into_company,client_region,client_name,contact,contact_person,client_demand,purchase_quantity,receive_person,state,remark,c_user_uid,c_time,del_flag)values(#{objuid},#{facCode},#{promoteCode},#{infoSource},#{sourceTime},#{intoCompany},#{clientRegion},#{clientName},#{contact},#{contactPerson},#{clientDemand},#{purchaseQuantity},#{receivePerson},#{state},#{remark},#{ccUserUid},#{ccTime},#{delFlag})";

    //修改时查询推广记录编号是否已经存在（不包含本身）
    public static final String QUERY_PRE_PROMOTE_BY_CODE2 = "select count(*) as count from pre_promote a where a.del_flag=0 and a.promote_code=#{promoteCode} and a.promote_code<>#{promoteCode2}";

    //修改推广记录
    public static final String UPDATE_PRE_PROMOTE = "update pre_promote a set a.promote_code=#{promoteCode},a.info_source=#{infoSource},source_time=#{sourceTime},a.into_company=#{intoCompany},a.client_region=#{clientRegion},a.client_name=#{clientName},a.contact=#{contact},a.contact_person=#{contactPerson},a.client_demand=#{clientDemand},a.purchase_quantity=#{purchaseQuantity},a.receive_person=#{receivePerson},a.remark=#{remark},a.m_user_uid=#{mmUserUid},a.m_time=#{mmTime} where a.objuid=#{objuid}";

    //删除推广记录
    public static final String DELETE_PRE_PROMOTE = "update pre_promote a set a.del_flag=1 where a.objuid=#{objuid}";

    //提交推广记录
    public static final String SUBMIT_PRE_PROMOTE = "update pre_promote a set a.state=1 where a.objuid=#{objuid}";

    //根据主键查询推广记录唯一数据
    public static final String QUERY_PRE_PROMOTE_BY_UID = "select a.objuid,a.promote_code,a.info_source,date_format(a.source_time,'%Y-%m-%d') as source_time,a.into_company,a.base_region_objuid as client_region_,case when a.base_region_objuid is null then a.client_region else a.region_name end as client_region,a.crm_base_objuid as client_name_,case when a.crm_base_objuid is null then a.client_name else a.crm_name end as client_name,a.contact,a.contact_person,a.client_demand,a.purchase_quantity,a.receive_person_,a.receive_person,a.remark from (select a.objuid,a.promote_code,a.info_source,a.source_time,a.into_company,a.client_region,a.client_name,a.contact,a.contact_person,a.client_demand,a.purchase_quantity,a.receive_person as receive_person_,c.emp_name as receive_person,a.remark,b.region_name,b.objuid as base_region_objuid,d.crm_name,d.objuid as crm_base_objuid from pre_promote a left join base_region b on b.objuid=a.client_region left join fac_employee c on c.objuid=a.receive_person left join crm_base d on d.objuid=a.client_name where a.del_flag=0) a where 1=1 and a.objuid=#{uid}";

    //根据条件查询推广记录所有数据条数
    public static final String QUERY_PRE_PROMOTE_ALL_COUNT = "<script> select count(*) as count from (select a.objuid,a.promote_code,a.info_source,date_format(a.source_time,'%Y-%m-%d') as source_time,a.into_company,a.client_region,a.client_name,a.contact,a.contact_person,a.client_demand,a.purchase_quantity,c.emp_name as receive_person,a.remark,b.region_name,b.objuid as objuid_ from pre_promote a left join base_region b on b.objuid=a.client_region left join fac_employee c on c.objuid=a.receive_person left join crm_base d on d.objuid=a.client_name where a.del_flag=0 and a.state=0) a where 1 = 1 <if test=\"infoSource!=''\"> and upper(a.info_source) like upper(concat('%',#{infoSource},'%')) </if> <if test=\"sourceTime!=''\"> and upper(a.source_time) like upper(concat('%',#{sourceTime},'%')) </if> <if test=\"intoCompany!=''\"> and upper(a.into_company) like upper(concat('%',#{intoCompany},'%')) </if> </script>";

    //根据条件查询推广记录所有数据
    public static final String QUERY_PRE_PROMOTE_ALL = "<script> select a.objuid,a.promote_code,a.info_source,a.source_time,a.into_company,case when a.base_region_objuid is null then a.client_region else a.region_name end as client_region,case when a.crm_base_objuid is null then a.client_name else a.crm_name end as client_name,a.contact,a.contact_person,a.client_demand,a.purchase_quantity,a.receive_person,a.state,a.remark from (select a.objuid,a.promote_code,a.info_source,date_format(a.source_time,'%Y-%m-%d') as source_time,a.into_company,a.client_region,a.client_name,a.contact,a.contact_person,a.client_demand,a.purchase_quantity,c.emp_name as receive_person,a.state,a.remark,b.region_name,b.objuid as base_region_objuid,d.crm_name,d.objuid as crm_base_objuid from pre_promote a left join base_region b on b.objuid=a.client_region left join fac_employee c on c.objuid=a.receive_person left join crm_base d on d.objuid=a.client_name where a.del_flag=0 and a.state=0) a where 1 = 1 <if test=\"infoSource!=''\"> and upper(a.info_source) like upper(concat('%',#{infoSource},'%')) </if> <if test=\"sourceTime!=''\"> and upper(a.source_time) like upper(concat('%',#{sourceTime},'%')) </if> <if test=\"intoCompany!=''\"> and upper(a.into_company) like upper(concat('%',#{intoCompany},'%')) </if> order by a.source_time desc limit #{page},#{limit} </script>";

    //根据条件查询推广记录所有数据条数
    public static final String QUERY_PRE_PROMOTE_ALL_COUNT2 = "<script> select count(*) as count from (select a.objuid,a.promote_code,a.info_source,date_format(a.source_time,'%Y-%m-%d') as source_time,a.into_company,a.client_region,a.client_name,a.contact,a.contact_person,a.client_demand,a.purchase_quantity,c.emp_name as receive_person,a.state,e.emp_name as sales_manager,dispatch_time,f.emp_name as sales_person,a.remark,b.region_name,b.objuid as base_region_objuid,d.crm_name,d.objuid as crm_base_objuid from pre_promote a left join base_region b on b.objuid=a.client_region left join fac_employee c on c.objuid=a.receive_person left join crm_base d on d.objuid=a.client_name left join fac_employee e on e.objuid=a.sales_manager left join fac_employee f on f.objuid=a.sales_person where a.del_flag=0) a where 1 = 1 <if test=\"infoSource!=''\"> and upper(a.info_source) like upper(concat('%',#{infoSource},'%')) </if> <if test=\"sourceTime!=''\"> and upper(a.source_time) like upper(concat('%',#{sourceTime},'%')) </if> <if test=\"intoCompany!=''\"> and upper(a.into_company) like upper(concat('%',#{intoCompany},'%')) </if> <if test=\"state!=''\"> and upper(a.state) like upper(concat('%',#{state},'%')) </if> </script>";

    //根据条件查询推广记录所有数据
    public static final String QUERY_PRE_PROMOTE_ALL2 = "<script> select a.objuid,a.promote_code,a.info_source,a.source_time,a.into_company,case when a.base_region_objuid is null then a.client_region else a.region_name end as client_region,case when a.crm_base_objuid is null then a.client_name else a.crm_name end as client_name,a.contact,a.contact_person,a.client_demand,a.purchase_quantity,a.receive_person,a.state,a.sales_manager,a.dispatch_time,a.sales_person,a.remark from (select a.objuid,a.promote_code,a.info_source,date_format(a.source_time,'%Y-%m-%d') as source_time,a.into_company,a.client_region,a.client_name,a.contact,a.contact_person,a.client_demand,a.purchase_quantity,c.emp_name as receive_person,a.state,e.emp_name as sales_manager,dispatch_time,f.emp_name as sales_person,a.remark,b.region_name,b.objuid as base_region_objuid,d.crm_name,d.objuid as crm_base_objuid from pre_promote a left join base_region b on b.objuid=a.client_region left join fac_employee c on c.objuid=a.receive_person left join crm_base d on d.objuid=a.client_name left join fac_employee e on e.objuid=a.sales_manager left join fac_employee f on f.objuid=a.sales_person where a.del_flag=0) a where 1 = 1 <if test=\"infoSource!=''\"> and upper(a.info_source) like upper(concat('%',#{infoSource},'%')) </if> <if test=\"sourceTime!=''\"> and upper(a.source_time) like upper(concat('%',#{sourceTime},'%')) </if> <if test=\"intoCompany!=''\"> and upper(a.into_company) like upper(concat('%',#{intoCompany},'%')) </if> <if test=\"state!=''\"> and upper(a.state) = upper(#{state}) </if> order by a.source_time desc limit #{page},#{limit} </script>";

    //根据条件查询推广记录所有数据条数
    public static final String QUERY_PRE_PROMOTE_ALL_COUNT3 = "<script> select count(*) as count from (select a.objuid,a.promote_code,a.info_source,date_format(a.source_time,'%Y-%m-%d') as source_time,a.into_company,a.client_region,a.client_name,a.contact,a.contact_person,a.client_demand,a.purchase_quantity,c.emp_name as receive_person,a.remark,b.region_name,b.objuid as objuid_ from pre_promote a left join base_region b on b.objuid=a.client_region left join fac_employee c on c.objuid=a.receive_person left join crm_base d on d.objuid=a.client_name where a.del_flag=0 and a.state=1) a where 1 = 1 <if test=\"infoSource!=''\"> and upper(a.info_source) like upper(concat('%',#{infoSource},'%')) </if> <if test=\"sourceTime!=''\"> and upper(a.source_time) like upper(concat('%',#{sourceTime},'%')) </if> <if test=\"intoCompany!=''\"> and upper(a.into_company) like upper(concat('%',#{intoCompany},'%')) </if> </script>";

    //根据条件查询推广记录所有数据
    public static final String QUERY_PRE_PROMOTE_ALL3 = "<script> select a.objuid,a.promote_code,a.info_source,a.source_time,a.into_company,case when a.base_region_objuid is null then a.client_region else a.region_name end as client_region,case when a.crm_base_objuid is null then a.client_name else a.crm_name end as client_name,a.contact,a.contact_person,a.client_demand,a.purchase_quantity,a.receive_person,a.state,a.remark from (select a.objuid,a.promote_code,a.info_source,date_format(a.source_time,'%Y-%m-%d') as source_time,a.into_company,a.client_region,a.client_name,a.contact,a.contact_person,a.client_demand,a.purchase_quantity,c.emp_name as receive_person,a.state,a.remark,b.region_name,b.objuid as base_region_objuid,d.crm_name,d.objuid as crm_base_objuid from pre_promote a left join base_region b on b.objuid=a.client_region left join fac_employee c on c.objuid=a.receive_person left join crm_base d on d.objuid=a.client_name where a.del_flag=0 and a.state=1) a where 1 = 1 <if test=\"infoSource!=''\"> and upper(a.info_source) like upper(concat('%',#{infoSource},'%')) </if> <if test=\"sourceTime!=''\"> and upper(a.source_time) like upper(concat('%',#{sourceTime},'%')) </if> <if test=\"intoCompany!=''\"> and upper(a.into_company) like upper(concat('%',#{intoCompany},'%')) </if> order by a.source_time desc limit #{page},#{limit} </script>";

    //查询人员信息（临时查询，正常应该在人员接口中）
    public static final String QUERY_BASE_REGION_ALL = "select a.objuid,concat(b.region_name,' / ',a.region_name) as region_name from base_region a left join base_region b on b.objuid=a.parent_uid where b.objuid is not null order by b.region_name asc, a.region_name";

    //查询人员信息（临时查询，正常应该在人员接口中）
    public static final String QUERY_FAC_EMPLOYEE_ALL = "select a.objuid,a.emp_name from fac_employee a where a.del_flag=0 order by a.emp_name asc";

    //派工推广记录
    public static final String ASSIGN_PRE_PROMOTE = "update pre_promote a set a.state=#{state}, a.sales_manager=#{salesManager}, a.dispatch_time=#{dispatchTime}, a.sales_person=#{salesPerson} where a.objuid=#{objuid}";

    //查询客户名称信息（临时查询，正常应该在客户接口中）
    public static final String QUERY_CRM_BASE_NAME = "select a.objuid, a.crm_name from crm_base a where a.del_flag=0 order by a.crm_name asc";

    //查询客户联系人、联系方式信息（临时查询，正常应该在客户接口中）
    public static final String QUERY_CRM_BASE2 = "select a.contact_person,a.contact_number from crm_base a where a.objuid=#{objuid}";

    //根据主键查询推广记录，用于询盘回访界面的显示
    public static final String QUERY_PRE_PROMOTE_BY_UID2 = "select a.objuid,a.info_source,a.source_time,a.into_company,a.client_region,a.client_name,a.contact,a.contact_person,a.client_demand,a.purchase_quantity,a.remark,b.emp_name as c_user_uid,a.c_time from pre_promote a left join fac_employee b on b.objuid=a.c_user_uid where a.objuid=#{objuid}";

    //修改推广记录表人员主键
    public static final String UPDATE_PRE_PROMOTE_CRM_BASE = "update pre_promote a set a.crm_base_uid=#{crmBaseUid} where a.objuid=#{objuid}";

}
